ERROR 1442 (HY000): Can't update table '...' in stored function/trigger because it is already used
一、ＭySQL错误现象：
MySQL执行创建的触发器时，报如下错误：ERROR 1442 (HY000): Can't update table 'tmp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
下面为错误触发器的创建过程：
1、创建测试表格：
drop table if exists tmp; create table tmp (id int, n1 int, n2 int); insert tmp values(1, 10, 50); MariaDB [test]> select * from tmp; +------+------+------+ | id | n1 | n2 | +------+------+------+ | 1 | 10 | 50 | +------+------+------+ 1 row in set (0.01 sec)
2、创建一个MySQL触发器：
DELIMITER $ drop trigger if exists tmp_update$ create trigger tmp_update after update on tmp for each row begin update tmp set n2=n1*5 where id=new.id; end$ DELIMITER ;
３、测试触发效果：
mysql> select * from tmp; +------+------+------+ | id | n1 | n2 | +------+------+------+ | 1 | 10 | 50 | +------+------+------+ 1 row in set (0.00 sec) mysql> update tmp set n1=2 where id=1; ERROR 1442 (HY000): Can't update table 'tmp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
二、错误原因：
这是由于MySQL触发器，触发事件的表与触发更新的表是用一表。触发更新同一表时，不应该使用update语句更新，应该使用set修改NEW对象更新即可。三、解决方案：
1、将触发器的创建SQL语句改成如下并执行创建即可：DELIMITER $
drop trigger if exists tmp_update$
create trigger tmp_update
before update on tmp -- 更新之前执行，这样才能在NEW插入到数据库之前，修改NEW.n2
for each row
begin
set new.n2 = new.n1*5; -- 直接修改new.n2
end$
DELIMITER ; 触发器测试效果：aiezu.com> select * from tmp; +------+------+------+ | id | n1 | n2 | +------+------+------+ | 1 | 10 | 50 | +------+------+------+ 1 row in set (0.00 sec) aiezu.com> update tmp set n1=12 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 aiezu.com> select * from tmp; +------+------+------+ | id | n1 | n2 | +------+------+------+ | 1 | 12 | 60 | +------+------+------+ 1 row in set (0.00 sec)
